# This function creates the SQL code to create table if needed, based on OUTPUT_TABLE_FLAG
makeSQL_tableCreation () {
    local sql OUTPUT_TABLE_FLAG OUTPUT_TABLE
    OUTPUT_TABLE_FLAG=$1;
    OUTPUT_TABLE=$2;
    if  [[ "$OUTPUT_TABLE_FLAG" -eq 1 ]];
    then
        sql="create table $OUTPUT_TABLE as ";
    else
        sql=""
    fi;
    echo "$sql"
}

# This function takes an array as input, and returns the same elements with a prefix for each
addPrefix () {
    local mystring
    prefix=$1
    shift 1
    ARGS=( "$@" )
    NUM_ARGS=${#ARGS[@]}
    for i in $(seq 0 $((NUM_ARGS-1)));
    #for ((i=0;i<NUM_ARGS;i++));
        do
           mystring="$mystring ${prefix}${ARGS[$i]}"
    done
    echo "$mystring"
}

# This function concatenates values (and adds a separator)
concatenateValues () {
    local sql ARGS NUM_ARGS i
    sep=$1;
    shift 1
    ARGS=( "$@" )
    NUM_ARGS=${#ARGS[@]}
    sql=""
    for i in $(seq 0 $((NUM_ARGS-1))); do
    #for ((i=0;i<NUM_ARGS;i++)); do
        if  [[ "$i" -ne 0 ]];
        #then sql="${sql} $sep"
        then sql="${sql}${sep}"
        fi;
        #sql="$sql ${ARGS[$i]}"
        sql="${sql}${ARGS[$i]}"
    done
    echo "$sql"
}



fieldNames2FieldEqualities () {
    local sql DB_NAME
    sql=""
    ARGS=( "$@" )
    NUM_ARGS=${#ARGS[@]}
    #for ((i=0;i<NUM_ARGS;i++));
    for i in $(seq 0 $((NUM_ARGS-1)));
        do sql="$sql t1.${ARGS[$i]}=t2.${ARGS[$i]}"
    done
    echo "$sql"
}

dropTables () {
    local DB_NAME
    DB_NAME=$1
    shift 1
    TABLES=( "$@" )
    NUM_TABLES=${#TABLES[@]}
    #for ((i=0;i<NUM_TABLES;i++));
    for i in $(seq 0 $((NUM_TABLES-1)));
        do sqlite3 $DB_NAME "drop table ${TABLES[$i]};"
    done
}

renameTable () {
    sqlite3 $1 "alter table $2 rename to $3;"
}

# Example:
# viewFirstRows mydb.db mytable 500
viewFirstRows () {
    sqlite3 $1 "select * from $2 where rowId <= $3;"
}

# Example
# selectColumns mydb.db 1 myOutputTable myInputTable col1 col2 col3
selectColumns () {
    local sql sql1 sql2 DB_NAME IN_TABLE FIELDS
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE=$4
    shift 4
    FIELDS="$@"
    sql2=$( concatenateValues , ${FIELDS[@]} )
    sql="$sql1 select $sql2 from $IN_TABLE;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# Example
# sortTable mydb.db myInputTable col1 col2 col3
sortTable () {
    local sql sql1 DB_NAME IN_TABLE FIELDS
    DB_NAME=$1
    IN_TABLE=$2
    shift 2
    FIELDS="$@"
    sql1=$( concatenateValues , ${FIELDS[@]} )
    sql="create table ${IN_TABLE}_xxx2 as select * from $IN_TABLE order by $sql1;"
#    echo "$sql"
    sqlite3 $DB_NAME "$sql"
    dropTables $DB_NAME $IN_TABLE
    renameTable $DB_NAME ${IN_TABLE}_xxx2 $IN_TABLE
}


# Split a big table (lots of rows) into tables with N rows (except perhaps for the last one)
splitTableRows () {
    local DB_NAME INTABLE NUM_TTL_ROWS NUM_OUT_TABLES sql
    DB_NAME=$1
    INTABLE=$2
    NUM_TTL_ROWS=$3
    NUM_OUT_TABLES=$4
    NUMROWS_PER_TABLE=$(echo "if ( $NUM_TTL_ROWS%$NUM_OUT_TABLES ) $NUM_TTL_ROWS/$NUM_OUT_TABLES+1 else $NUM_TTL_ROWS/$NUM_OUT_TABLES" | bc)
    offset=0
    #for ((i=1;i<=NUM_OUT_TABLES;i++));
    for i in $(seq 1 $NUM_OUT_TABLES);
        do 
           row_max=$((offset+NUMROWS_PER_TABLE))
         sql="create table ${INTABLE}_subset_$i as select * from $INTABLE where rowId > $offset and rowId <= $row_max"
         echo "$sql"
         sqlite3 $DB_NAME "$sql"
        offset=$((offset+NUMROWS_PER_TABLE))
    done
}    
    
   



# This function creates a new table as the cartesian product of multiple input tables.
# Examples of use:
#    cartesianProduct mydb.db 1 outTable inTable1 inTable2 inTable3
#    cartesianProduct mydb.db 0 0 inTable1 inTable2 inTable3
cartesianProduct () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    star=\*
    sql1=$( makeSQL_tableCreation $2 $3 )
    shift 3
    IN_TABLES=( "$@" )
    sql2=$( concatenateValues , ${IN_TABLES[@]} )
    sql="$sql1 select $star from  $sql2"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# countRows mydb.db myTable
countRows () {
    local sql
    sql="select count(*) from $2;"
    #echo "$sql"
    sqlite3 $1 "$sql"
}

# This function counts the number of distinct combinations of field values in a table.
# Examples of use:
#countDistinct mydb.db 1 outTable listings listingId hostId
#countDistinct mydb.db 0 0 listings listingId hostId
countDistinct () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE=$4
    shift 4
    FIELDS=( "$@" )
    star=\*
    sql2=$( concatenateValues , ${FIELDS[@]} )
    sql="$sql1 select count($star) from (select distinct $sql2 from $IN_TABLE);"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# This function creates a new table with the distinct combinations of field values in that table.
# Examples of use:
#selectDistinct mydb.db 1 outTable listings listingId hostId
#selectDistinct mydb.db 0 0 listings listingId hostId
selectDistinct () {
    local sql sql2 DB_NAME
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE=$4
    shift 4
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )
    sql="$sql1 select distinct $sql2 from $IN_TABLE order by $sql2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# Examples of use:
#selectUnion mydb.db 1 outTable tab1 tab2 f1 f2 f3
#selectUnion mydb.db 0 0 tab1 tab2 f1 f2 f3
selectUnion () {
    local sql sql1 sql2 FIELDS IN_TABLE1 IN_TABLE2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE1=$4
    IN_TABLE2=$5
    shift 5
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )
    star=\*
    sql="$sql1 select $sql2 from $IN_TABLE1 union select $sql2 from $IN_TABLE2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql;"
}


# Examples of use:
#selectIntersection mydb.db 1 outTable tab1 tab2 f1 f2 f3
#selectIntersection mydb.db 0 0 tab1 tab2 f1 f2 f3
selectIntersection () {
    local sql sql1 sql2 FIELDS IN_TABLE1 IN_TABLE2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE1=$4
    IN_TABLE2=$5
    shift 5
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )
    star=\*
    sql="$sql1 select $sql2 from $IN_TABLE1 INTERSECT select $sql2 from $IN_TABLE2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql;"
}

# Examples of use:
#selectExcept mydb.db 1 outTable tab1 tab2 f1 f2 f3
#selectExcept mydb.db 0 0 tab1 tab2 f1 f2 f3
selectExcept () {
    local sql sql1 sql2 FIELDS IN_TABLE1 IN_TABLE2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE1=$4
    IN_TABLE2=$5
    shift 5
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )
    star=\*
    sql="$sql1 select $sql2 from $IN_TABLE1 except select $sql2 from $IN_TABLE2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql;"
}


# This function merges two tables based on a JOIN (all field values have to be equal)
# Examples of use:
#equalJoin mydb.db 1 outTable inTable1 inTable2 field1 field2
#equalJoin mydb.db 0 0 inTable1 inTable2 field1 field2
equalJoin () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE1=$4
    IN_TABLE2=$5
    shift 5
    FIELDS=( "$@" )
    FIELD_EQUALITIES=$( fieldNames2FieldEqualities ${FIELDS[@]} )
    star=\*
    sql2=$( concatenateValues " AND " ${FIELD_EQUALITIES[@]} )
    sql="$sql1 select $star from $IN_TABLE1 t1, $IN_TABLE2 t2 where $sql2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}



# This script merges two tables on a Table1 LEFT JOIN Table2 (all field values have to be equal)
# Examples of use:
#leftJoin mydb.db 1 outTable inTable1 inTable2 field1 field2 field3
#leftJoin mydb.db 0 0 inTable1 inTable2 field1
leftJoin () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE1=$4
    IN_TABLE2=$5
    shift 5
    FIELDS=( "$@" )
    FIELD_EQUALITIES=$( fieldNames2FieldEqualities ${FIELDS[@]} )
    star=\*
    sql2=$( concatenateValues " AND " ${FIELD_EQUALITIES[@]} )
    sql="$sql1 select t1.$star, t2.$star from $IN_TABLE1 t1 left join $IN_TABLE2 t2 on $sql2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}


# This function calculates one aggregate from a table, grouping by a variable number of fields.
# Examples of use:
#calcAggregate mydb.db 1 outTable inTable1 COUNT field1
#calcAggregate mydb.db 1 outTable inTable1 MAX PRICE field1 field2
calcAggregate () {
    local sql sql1 sql2 sql3 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE=$4
    shift 4
    
    arg=( "$@" )
    star=\*
    # Read AGG_TYPE AND AGG_FIELD (if there is one)
    idx=0
    AGG_TYPE="${arg[$idx]}"
    if  [[ "$AGG_TYPE" == "COUNT" ]];
    then
        sql3="COUNT($star)";
        AGG_LABEL='mycount'
    else
        idx=$(($idx+1))
        AGG_FIELD="${arg[$idx]}"
        sql3="$AGG_TYPE($AGG_FIELD)";
        AGG_LABEL="${AGG_TYPE}_${AGG_FIELD}"
    fi;
    idx=$(($idx+1))

    # Read FIELD(S)
    shift $idx
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )

    sql="$sql1 select $sql2, $sql3 as $AGG_LABEL FROM $IN_TABLE GROUP BY $sql2;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# Example use:
#   calcShares mydb.db 1 output input value groupingField1 groupingField2
#   calcShares mydb.db 1 output input 1 groupingField1
calcShares () {
    ##############################################
    # This function takes an input table where some columns are "grouping fields" (Col1, ..., ColN),
    # one column is a numeric field. The function outputs the same table with one column added,
    # which is the share of the row's numeric field within its group.
    # For example: this can be used to calculate the market share of a product within a set a store.
    # To do this, the function first calculates the sum of the numeric field for each group,
    # and then divide each row by the corresponding sum.
    ### Inputs:
    # INPUT_TABLE should be: Col1, Col2,...,,ColN, otherColumn1, otherColumn2,..., Field
    ### Outputs:
    # OUTPUT_TABLE should be: Col1, Col2,...,,ColN, otherColumn1, otherColumn2,..., Field, Share
    #    such that the sum of the shares add up to 1 within a group defined by (Col1, ..., ColN)
    ##############################################
    local sql1 sql2 sql DB_NAME INPUT_TABLE VALUE_FIELD FIELDS T1_FIELDS FIELD_EQUALITIES
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    INPUT_TABLE=$4
    VALUE_FIELD=$5
    shift 5
    GROUPING_FIELDS=( "$@" )
    
    sql2=$( concatenateValues " " ${GROUPING_FIELDS[@]} )
    
    # Treat separately the case where $VALUE_FIELD == 1  (do count instead of SUM(value))
    if  [[ "$VALUE_FIELD" == "1" ]];
    then
        calcAggregate $DB_NAME 1 putInPctage_t2 $INPUT_TABLE COUNT "$sql2"
        RATIO="1.0/t2.mycount"
    else
        calcAggregate $DB_NAME 1 putInPctage_t2 $INPUT_TABLE SUM $VALUE_FIELD "$sql2"
        RATIO="1.0*t1.$VALUE_FIELD/t2.SUM_$VALUE_FIELD"
    fi;
    
    # Treat separately the case where $GROUPING_FIELDS == 1  (no grouping)
        if  [[ "$GROUPING_FIELDS" == "1" ]];
    then
        FIELD_EQUALITIES=""
    else
        FIELD_EQUALITIES=$( fieldNames2FieldEqualities ${GROUPING_FIELDS[@]} )
        FIELD_EQUALITIES=$( concatenateValues " AND " ${FIELD_EQUALITIES[@]} )
        FIELD_EQUALITIES="where $FIELD_EQUALITIES"
    fi;
    
    sql="$sql1 select t1.*, $RATIO as Pctage_$VALUE_FIELD \
        from $INPUT_TABLE t1, putInPctage_t2 t2 $FIELD_EQUALITIES;"
    echo $sql
    sqlite3 $MYDB "$sql"
    dropTables $DB_NAME putInPctage_t2
}



# Examples of use:
#reduceByField mydb.db 1 outTable myTable myField lookupTable
reduceByField () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    TABLE1=$4
    FIELD=$5
    TABLE2=$6
    star=\* 
    sql="$sql1 select $star from $TABLE1 where $FIELD in (select distinct $FIELD from $TABLE2);"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

removeByField () {
    local sql sql1 sql2 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    TABLE1=$4
    FIELD=$5
    TABLE2=$6
    star=\* 
    sql="$sql1 select $star from $TABLE1 where $FIELD NOT IN (select distinct $FIELD from $TABLE2);"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}



# This function finds duplicates in a table, grouping by a number of fields.
# Examples of use:
#findDuplicates mydb.db 1 outTable inTable1 field1 field2
findDuplicates () {
    local sql sql1 sql2 sql3 DB_NAME star
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    IN_TABLE=$4
    shift 4
    FIELDS=( "$@" )
    sql2=$( concatenateValues , ${FIELDS[@]} )

    sql="$sql1 select $sql2 FROM $IN_TABLE GROUP BY $sql2 having count(*) > 1;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}


########
# This function keeps only one row per group in a given table, based on a given criterion (MIN or MAX of a field).
# For example, suppose that I have a table "cars", with carId, mileage, last_update (where last_update) is a string giving a date.
# If I want to keep only one row per carId (note: I could also do a group by with more than one field!), then
# I can do:
#    keepSingleRow4Group mydb.db 1 outputTable cars MAX last_update carId
# More generally:
#    keepSingleRow4Group $MYDB 1 outputTable inputTable MAX(or MIN) criterionField groupField1 groupField2... groupFieldN
keepSingleRow4Group () {
    local DB_NAME INPUT_TABLE CRITERION_TYPE CRITERION_FIELD
    DB_NAME=$1
    sql1=$( makeSQL_tableCreation $2 $3 )
    INPUT_TABLE=$4
    CRITERION_TYPE=$5
    CRITERION_FIELD=$6
    shift 6
     GROUP_BY_FIELDS=( "$@" )
    
    # Find MAX/MIN for each group
      calcAggregate $DB_NAME 1 ztmp_keepSingle1 $INPUT_TABLE $CRITERION_TYPE $CRITERION_FIELD "${GROUP_BY_FIELDS[@]}"
    
    FIELD_EQUALITIES=$( fieldNames2FieldEqualities ${GROUP_BY_FIELDS[@]} )
    sql2=$( concatenateValues " AND " ${FIELD_EQUALITIES[@]} )
    
    star=\* 
    sql="$sql1 select t1.$star from $INPUT_TABLE t1, ztmp_keepSingle1 t2 where $sql2 \
        AND t1.$CRITERION_FIELD = t2.${CRITERION_TYPE}_$CRITERION_FIELD;"
    echo $sql
     sqlite3 $DB_NAME "$sql"
    
    # Drop table with MAX/MIN for each group
    dropTables $DB_NAME ztmp_keepSingle1    
    echo "If there is a tie for a given group, there will be multiple rows. Please check."
}




# This script creates a dictionary: unique values of a field get associated a code, and that code gets added to the table.
# A file is output with the correspondence between values and codes.
addCodes4Values () {
    local DB_NAME
    DB_NAME=$1
    TABLE=$2
    FIELD=$3
    OUTPUT_FILE_PATH=$4
    selectDistinct $DB_NAME 1 ${FIELD}_codes0 $TABLE $FIELD    
    sqlite3 $DB_NAME "create table ${FIELD}_codes as select $FIELD, rowId as ${FIELD}Code from ${FIELD}_codes0;"
    dropTables $DB_NAME ${FIELD}_codes0
    renameTable $DB_NAME $TABLE replaceString0
    equalJoin $DB_NAME 1 $TABLE replaceString0 ${FIELD}_codes $FIELD 
    dropTables $DB_NAME replaceString0
    
    # Output a dictionary file with correspondence between unique values and corresponding codes
    sqlite3 $DB_NAME -csv -header "select * from ${FIELD}_codes" >${OUTPUT_FILE_PATH}/${FIELD}_codes.csv
    
    dropTables $DB_NAME ${FIELD}_codes
}


# From a table with a combination of ids (id1, id2... idN) where all combinations of ids don't necessarily appear
# (some may be missing), this function creates a table with the full cartesian product of id combinations, where missing values
# are inserted if the combo is absent from the original table.
# Example use: fillCartesianProduct mydb.db 1 output input id1 id2 ... idN
fillCartesianProduct () {
    echo "WARNING: This assumes that there is no duplicate in the input table!"
    local sql sql11 sql2 sql3 DB_NAME star INPUT_TABLE ID_FIELDS ID_SET
    DB_NAME=$1
    sql11=$( makeSQL_tableCreation $2 $3 )
    INPUT_TABLE=$4
    shift 4
    ID_FIELDS=( "$@" )
    
    # Loop over ids, make a table with unique values of id
    NUM_ID_FIELDS=${#ID_FIELDS[@]}
    ID_TABLES=()
    #for ((i=0;i<NUM_ID_FIELDS;i++));
    for i in $(seq 0 $((NUM_ID_FIELDS-1)));
        do 
        selectDistinct $DB_NAME 1 ztmpfillCartes_ids_$i $INPUT_TABLE "${ID_FIELDS[$i]}"
        ID_TABLES=( "${ID_TABLES[@]}" "ztmpfillCartes_ids_$i" )
    done
    
    # Make full cartesian
     cartesianProduct $DB_NAME 1 ztmpfillCartes_idsCombos "${ID_TABLES[@]}"
    
    # Do a left join
    FIELD_EQUALITIES=$( fieldNames2FieldEqualities ${ID_FIELDS[@]} )
    star=\*
    sql2=$( concatenateValues " AND " ${FIELD_EQUALITIES[@]} )
    sql3=$( concatenateValues , ${ID_FIELDS[@]} )
     sql="$sql11 select t1.$star, t2.$star from ztmpfillCartes_idsCombos t1 left join $INPUT_TABLE t2 on $sql2 order by $sql3;"
     echo $sql
      sqlite3 $MYDB "$sql"
      
      # Clean up
      dropTables $MYDB ztmpfillCartes_idsCombos
      #for ((i=0;i<NUM_ID_FIELDS;i++));
      for i in $(seq 0 $((NUM_ID_FIELDS-1)));
        do 
        dropTables $MYDB ztmpfillCartes_ids_$i 
    done
}

# Copies a table from DB1 to DB2
# Example use: copyTable2otherDB mydb1.db mydb2.db mytable
copyTable2otherDB () {
    sqlite3 $1 ".dump $3" | sqlite3 $2
}


###################################################################################################################
# This script imports a CSV file and makes a new table from it. If a table with the same already exists, it is first deleted.
# Example of use: importCSVFileInDB mydb.db myOutputTable myInputFile.csv
importCSVFileInDB () {
    local DB_NAME TABLE FILE
    DB_NAME=$1
    TABLE=$2
    FILE=$3

    # Drop table if exists
    sqlite3 $DB_NAME "drop table if exists $TABLE"

    # Import file into new table
    sqlite3 $DB_NAME -csv ".import $FILE $TABLE"
}

# stackTable2BigTable mydb.db BIG_TABLE NEW_TABLE
stackTable2BigTable () {
    local sql star
    star=\*
    sql="insert into $2 select $star from $3;"
    echo "$sql"
    sqlite3 $1 "$sql;"
}


# Example of use: stackCSVFileOnTable mydb.db myOutputTable myInputFile.csv
stackCSVFileOnTable () {
    local DB_NAME BIG_TABLE NEW_FILE
    DB_NAME=$1
    BIG_TABLE=$2
    NEW_FILE=$3
    NEW_TABLE=new_${BIG_TABLE}

    # Import file into new table
    sqlite3 $DB_NAME -csv ".import $NEW_FILE $NEW_TABLE"

    # Dump new table into big table and drop new table
    sqlite3 $DB_NAME "insert into $BIG_TABLE select * from $NEW_TABLE; drop table $NEW_TABLE;"
}

# insertValues $DB_NAME persons FirstName \'Cecilia\' LastName \'Stourm\'
insertValues () {
    local DB_NAME TABLE sql1 sql2 ARGS NUM_ARGS NumPairs pairIdx fieldIdx valIdx field value sql
    DB_NAME=$1
    TABLE=$2
    shift 2
    ARGS=( "$@" )
    NUM_ARGS=${#ARGS[@]}
    
    if [ `echo "$NUM_ARGS % 2" | bc` -ne 0 ];
    then
        echo 'wrong input'
        exit 42;
    else
        sql1="";
        sql2="";
        NumPairs=$((NUM_ARGS/2));
        #for ((pairIdx=0;pairIdx<NumPairs;pairIdx++));
        for pairIdx in $(seq 0 $((NumPairs-1)));
        do
            if  [[ "$pairIdx" -ne 0 ]];
            then sql1="${sql1}, "; sql2="${sql2}, "
            fi;
            fieldIdx=`expr $pairIdx \\* 2`
            valIdx=`expr $pairIdx \\* 2 + 1`
            field=${ARGS[$fieldIdx]}
            value=${ARGS[$valIdx]}
            sql1="$sql1 $field"
            sql2="$sql2 $value"
        done
    fi;
    
     sql="insert into $TABLE ($sql1) values($sql2);"
     echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# editRow $DB_NAME persons personID=1 FatherId=5 MotherId=6
editRow () {
    local DB_NAME TABLE CONDITION sql  FIELD_VAL_PAIRS
    DB_NAME=$1
    TABLE=$2
    CONDITION=$3
    shift 3
     FIELD_VAL_PAIRS=( "$@" )
     sql2=$( concatenateValues , ${FIELD_VAL_PAIRS[@]} )
     
    sql="update $TABLE set $sql2 where $CONDITION;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# removeRows mydb.db myTable rowId rowId_val
removeRows () {
    local DB_NAME TABLE ID_FIELD ID_VAL
    DB_NAME=$1
    TABLE=$2
    ID_FIELD=$3
    ID_VAL=$4
    sql="delete from $TABLE where $ID_FIELD=$ID_VAL;"
    echo "$sql"
    sqlite3 $DB_NAME "$sql"
}

# importCSVFile mydb.db myFile.csv myTable
importCSVFile () {
    local DB_NAME FILE TABLE
    DB_NAME=$1
    FILE=$2
    TABLE=$3
    sqlite3 $DB_NAME -csv ".import $FILE $TABLE"
}

#importCSVFile2 mydb.db myFile.csv myTable "|"
#importCSVFile2 mydb.db myFile.csv myTable ";"
importCSVFile2 () {
    local DB_NAME FILE TABLE
    DB_NAME=$1
    FILE=$2
    TABLE=$3
    SEPARATOR=$4
    sqlite3 $DB_NAME -csv -separator "$SEPARATOR" ".import $FILE $TABLE"
}

# exportCSVFile mydb.db myTable myFile.csv
exportCSVFile () {
    local DB_NAME TABLE MYFILE
    DB_NAME=$1
    TABLE=$2
    MYFILE=$3
    sqlite3 $DB_NAME -csv -header "select * from $TABLE" >$MYFILE
}


##########
# sqlSchema mydb.db
# sqlSchema mydb.db mytable
sqlSchema () {
    sqlite3 $1 ".schema $2"
}
sqlTables () {
    sqlite3 $1 ".tables"
}

